#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb


#
# db_connect = None


class DBHelper:
    local_host = "192.168.3.101"
    user_name = "wku"
    pass_word = ""
    db_name = "foreign_trade"

    '''python操作mysql的增删改查的封装'''

    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        '''
        初始化参数
        :param host:        主机
        :param user:        用户名
        :param password:    密码
        :param database:    数据库
        :param port:        端口号，默认是3306
        :param charset:     编码，默认是utf8
        '''
        self.host = host
        self.port = port
        self.database = database
        self.user = user
        self.password = password
        self.charset = charset

    def connect(self):
        '''
        获取连接对象和执行对象
        :return:
        '''
        self.conn = MySQLdb.connect(host=self.host,
                                    user=self.user,
                                    password=self.password,
                                    database=self.database,
                                    port=self.port,
                                    charset=self.charset)

        self.cur = self.conn.cursor()

    def fetchone(self, sql, params=None):
        '''
         根据sql和参数获取一行数据
       :param sql:          sql语句
       :param params:       sql语句对象的参数元组，默认值为None
       :return:             查询的一行数据
       '''
        dataOne = None
        try:
            count = self.cur.execute(sql, params)
            if count != 0:
                dataOne = self.cur.fetchone()
        except Exception as ex:
            print(ex)
        finally:
            self.close()
        return dataOne

    def fetchall(self, sql, params=None):
        '''
         根据sql和参数获取一行数据
       :param sql:          sql语句
       :param params:       sql语句对象的参数列表，默认值为None
       :return:             查询的一行数据
       '''
        dataall = None
        try:
            count = self.cur.execute(sql, params)
            if count != 0:
                dataall = self.cur.fetchall()
        except Exception as ex:
            print(ex)
        finally:
            self.close()
        return dataall

    def __item(self, sql, params=None):
        '''
        执行增删改
        :param sql:           sql语句
        :param params:        sql语句对象的参数列表，默认值为None
        :return:              受影响的行数
        '''
        count = 0
        try:
            count = self.cur.execute(sql, params)
            self.conn.commit()
        except Exception as ex:
            print(ex)
        finally:
            self.close()
        return count

    def update(self, sql, params=None):
        '''
        执行修改
        :param sql:     sql语句
        :param params:  sql语句对象的参数列表，默认值为None
        :return:        受影响的行数
        '''
        return self.__item(sql, params)

    def insert(self, sql, params=None):
        '''
        执行新增
        :param sql:     sql语句
        :param params:  sql语句对象的参数列表，默认值为None
        :return:        受影响的行数
        '''
        return self.__item(sql, params)

    def delete(self, sql, params=None):
        '''
        执行删除
        :param sql:     sql语句
        :param params:  sql语句对象的参数列表，默认值为None
        :return:        受影响的行数
        '''
        return self.__item(sql, params)

    def close(self):
        '''
        关闭执行工具和连接对象
        '''
        if self.cur != None:
            self.cur.close()
        if self.conn != None:
            self.conn.close()
# 测试查询多条数据
# # 初始化对象
# helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')
# # 连接
# helper.connect()
# # sql
# sql = 'select * from t_user where name = %s and id > %s'
# # params
# params = ['小茗',1]
# # 执行
# data = helper.fetchall(sql, params)
# # 判断
# if data:
#     for temp in data:
#         print(temp)
# else:  # None,False,0
#     print('没有数据.')
#
# helper.close()



# 测试查询一条数据
#
# # 初始化对象
# helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')
# # 连接
# helper.connect()
# # sql
# sql = 'select * from t_user where id = %s'
# #sql = 'select * from t_user where id = 1'
# # params
# params = [2]
# # 执行
# data = helper.fetchone(sql, params)
# #data = helper.fetchone(sql)
# # 判断
# if data:
#     print(data)
# else:  # None,False,0
#     print('没有数据.')


# 测试增删改
# # 初始化对象
# helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')
# # 连接
# helper.connect()
# # sql
# sql = 'update t_user set name =%s,pwd=%s,birthday=%s where id=%s'
# # params
# id = input('输入编号:')
# name = input('输入姓名:')
# pwd = input('输入密码:')
# birthday = time.strptime(input('输入生日:'), '%Y年%m月%d日')
# params = [name, pwd, birthday,id]
# # 执行
# count = helper.update(sql, params)
# # 判断
# if count:
#     print('操作成功.')
# else:  # None,False,0
#     print('操作失败.')